Snowflake から Git リポジトリにアクセスし Jinja2 テンプレートを使用したファイルを実行する #SnowflakeDB

モダンデータスタック(MDS)

#Snowflake

安原朋紀

2024.07.06

はじめに

2024年1月のアップデートで、Snowflake 内から Git リポジトリにアクセスできる機能がプレビューとなりました。

ステージ上のファイルを実行できるEXECUTE IMMEDIATE FROMと組み合わせることで、Snowflake から Git リポジトリのファイルにアクセスし、ファイル内のステートメントを実行することができます。

さらに2024年3月のリリースではEXECUTE IMMEDIATE FROMで Jinja2 テンプレートファイルを使用した SQL スクリプトを実行できる機能もプレビューとなりました。
これにより、スクリプト内に変数や条件式、ループなどの制御構文を含めることができ、動的なスクリプトを生成できます。

こちらの機能を試してみたので記事としました。

Snowflake で Git リポジトリを使用する

Snowflake からの Git リポジトリへのアクセスの概要は以下に記載があります。

具体的には Snowflake 内にリポジトリステージと呼ばれる特別な種類のステージを作成することで、リモートリポジトリを Snowflake と統合することができます。
リポジトリステージは、ブランチ、タグ、コミットを含むリモートリポジトリの完全なクローンを持つローカルリポジトリとして機能します。

執筆時点では以下のバージョン管理システムを使用可能です。

GitHub
GitLab
BitBucket
Azure DevOps
AWS CodeCommit

本機能の主な制約は以下です。

Snowflake と Git リポジトリ間のやり取りは読み取り専用で書き込みは不可
Snowflake から Git リポジトリへのアクセスは、パブリックインターネット経由のみサポート
- プライベートネットワークの背後にあるリポジトリへのアクセスはサポートされていない

事前準備

検証用に Git 側で以下の作業を行っておきます。

リモートリポジトリの作成
アクセストークンの取得
ローカルリポジトリの作成

リモートリポジトリの作成

GitHub にログインし新しいリポジトリを作成します。ここではプライベートリポジトリとして作成しました。

snowflake-git-stage-jinja2-01

アクセストークンの取得

作成したリポジトリにアクセス可能な Personal access token を発行します。

snowflake-git-stage-jinja2-1

コンテンツへのアクセスは下図の通りとしました。

snowflake-git-stage-jinja2-2

ローカルリポジトリの作成

ローカルでローカルリポジトリを作成します。

mkdir snowflake-git-integration && cd $_
git init

サンプルとして以下のファイルを作成します。

createdb-test.sql

USE ROLE SYSADMIN;
USE WAREHOUSE COMPUTE_WH;
CREATE OR REPLACE DATABASE GITTEST;

リモートリポジトリに push します。

git add .
git commit -m "add sample sql"
git push -u origin master

snowflake-git-stage-jinja2-3

リポジトリステージの作成手順

Snowflake から Git リポジトリへのアクセスのセットアップ手順は以下に記載があるのでこちらを参考に進めます。

認証用の資格情報を含むシークレットを作成する

Git リポジトリに認証が必要な場合は、 Snowflake がリポジトリでの認証に使用できる資格情報を含むシークレットを作成します。シークレットはスキーマレベルのオブジェクトなので、ここでは以下のコマンドでシークレット作成用のデータベース・スキーマ、シークレット管理用のロールを作成しました。

--データベース作成
USE ROLE sysadmin;
----シークレット管理用DB・スキーマを作成
CREATE DATABASE IF NOT EXISTS governance;
CREATE SCHEMA IF NOT EXISTS integrations;

--管理用ロールを作成
USE ROLE useradmin;
CREATE ROLE IF NOT EXISTS git_admin;

--スキーマに対するシークレットの作成権限を付与
USE ROLE securityadmin;
GRANT CREATE SECRET ON SCHEMA governance.integrations TO ROLE git_admin;

--対象のデータベース・スキーマに対するUSAGE権限も必要なので付与
GRANT USAGE ON DATABASE governance TO ROLE git_admin;
GRANT USAGE ON SCHEMA governance.integrations TO ROLE git_admin;

--ロール階層を定義
GRANT ROLE git_admin TO ROLE sysadmin;

--コンテキストの設定（シークレット作成ロールに切り替え、スキーマを指定）
USE ROLE git_admin;
USE SCHEMA governance.integrations;

--シークレットを作成
CREATE OR REPLACE SECRET git_secret
  TYPE = password
  USERNAME = '<ユーザー名>'
  PASSWORD = '<トークン>';

snowflake-git-stage-jinja2-4

リポジトリ API とやり取りするための API 統合を作成する

Git リポジトリ API の使用には、API 統合を作成します。
デフォルトでは ACCOUNTADMIN のみが統合オブジェクトを作成可能です。
CREATE API INTEGRATION | Snowflake Documentation

他のロールで操作を行う場合は、アカウントレベルの CREATE INTEGRATION 権限をロールに付与します。
ここでは、上述の手順で作成したロールに統合オブジェクトを作成する権限を付与し統合オブジェクトを作成しました。

--CREATE INTEGRATION 権限を付与
USE ROLE ACCOUNTADMIN;
GRANT CREATE INTEGRATION ON ACCOUNT TO ROLE git_admin;

--API統合を作成
USE ROLE git_admin;
USE SCHEMA governance.integrations;

CREATE OR REPLACE API INTEGRATION git_api_integration
  API_PROVIDER = git_https_api
  API_ALLOWED_PREFIXES = ('https://github.com/<アカウント>')
  ALLOWED_AUTHENTICATION_SECRETS = (git_secret)
  ENABLED = TRUE;

snowflake-git-stage-jinja2-5

Git リポジトリステージを作成し、リポジトリをクローンする

Git リポジトリステージもスキーマレベルのオブジェクトです。そのためオブジェクトの作成には、スキーマに対する CREATE GIT REPOSITORY 権限が必要です。

また、リポジトリステージの作成時には他に以下の情報を使用するので確認しておきます。

リポジトリの origin
リポジトリで認証するときに Snowflake が使用する認証情報
- 上述の手順で作成済みのシークレット
Snowflake とリポジトリ API のやり取りの詳細を指定する API 統合
- こちらも上述の手順で作成済み

ここでは以下のコマンドでsnowflake_extensions の名称でリポジトリステージを作成しました。

--CREATE GIT REPOSITORY 権限を付与
USE ROLE securityadmin;
GRANT CREATE GIT REPOSITORY ON SCHEMA governance.integrations TO ROLE git_admin;

--コンテキストの設定
USE ROLE git_admin;
USE SCHEMA governance.integrations;

--GIT REPOSITORY ステージを作成
CREATE OR REPLACE GIT REPOSITORY snowflake_extensions
  API_INTEGRATION = git_api_integration
  GIT_CREDENTIALS = git_secret
  ORIGIN = 'https://github.com/<origin URL>';

作成後、UIでは下図のように確認できます。

snowflake-git-stage-jinja2-6

リポジトリステージに関する情報

snowflake-git-stage-jinja2-7

ブランチ名などの Git リポジトリのファイルパスやスコープを指定することで、ファイルを表示することができます。

ls @snowflake_extensions/branches/master;

snowflake-git-stage-jinja2-8

リポジトリステージを更新する

リモートリポジトリに変更があり、その内容をリポジトリステージに反映するにはALTER GIT REPOSITORY コマンドを使用します。これにより Git リポジトリのコンテンツをリポジトリステージにフェッチできます。

ALTER GIT REPOSITORY <リポジトリステージ名> FETCH;

EXECUTE IMMEDIATE FROM によるスクリプトの実行

ここまでの手順で、Snowflake リポジトリのファイルを取得できました。取得したファイルのスクリプトの実行にはEXECUTE IMMEDIATE FROMを使用できます。

アクセス権限の概要は以下の通りです。

EXECUTE IMMEDIATE FROM コマンド実行時のロールには、ファイルが配置されているステージに対するUSAGEやREAD権限が必要
- Stage privileges | Snowflake Documentation
ファイル実行時のロールは、権限を持つファイル内のステートメントのみを実行できる
- 例：ファイルに CREATE TABLEステートメントがある場合、ロールにはアカウント内にテーブルを作成するために必要な権限が必要。権限がない場合、ステートメントは失敗する。

以下のコマンドで指定のファイルを実行してみますました。

USE ROLE sysadmin;
USE WAREHOUSE compute_wh;
USE SCHEMA governance.integrations;
EXECUTE IMMEDIATE FROM @snowflake_extensions/branches/master/createdb-test.sql;

ファイルの内容は以下の通りです。

USE ROLE SYSADMIN;
CREATE OR REPLACE DATABASE GITTEST;

実行後、ファイルの内容に基づいてデータベースを作成することができました。

snowflake-git-stage-jinja2-9

クエリ履歴上は下図のようになっておりEXECUTE IMMEDIATE FROMコマンドに続いて SQL ファイル内のコマンド（赤枠）が順次実行されていることがわかります。

snowflake-git-stage-jinja2-10

Jinja2 テンプレートの使用

EXECUTE IMMEDIATE FROMでは、Jinja2 テンプレート使用したファイルを実行することも可能です。テンプレートとして変数や式、ループ、条件文、変数置換、マクロなどを使用できるため、スクリプトで定義されたオブジェクトのデプロイメントターゲットを動的に選択できます

スクリプトを Jinja テンプレートとして扱うためには、以下のいずれかをスクリプトに追加します。

--!jinja

または

#!jinja

例として、以下のファイル作成しリモートリポジトリにプッシュします。

jinja-test.sql

--!jinja
USE ROLE sysadmin;
USE DATABASE gittest;
CREATE SCHEMA {{sch_name}};

変更を反映させるために、リポジトリステージを更新します。

ALTER GIT REPOSITORY <リポジトリステージ名> FETCH;

snowflake-git-stage-jinja2-11

パラメータ値を指定する際はUSING ( <key> => <value> [ , <key> => <value> [ , ... ] ] ) として指定します。
以下のコマンドを実行してみます。

USE ROLE sysadmin;
USE WAREHOUSE compute_wh;
USE SCHEMA governance.integrations;

EXECUTE IMMEDIATE FROM @snowflake_extensions/branches/master/jinja-test.sql
    USING (sch_name =>'dev');

すると下図のように指定の値で（dev）スキーマが作成されます。

snowflake-git-stage-jinja2-12

実行時に変数値を指定できるので、別の値を指定し再度実行してみます。

USE SCHEMA governance.integrations;
EXECUTE IMMEDIATE FROM @snowflake_extensions/branches/master/jinja-test.sql
    USING (sch_name =>'prd');

変数をもとにスクリプトが実行され、異なる名称のスキーマとして作成できます。

snowflake-git-stage-jinja2-14

条件とループを含むテンプレートの例

Jinja2 テンプレートではループや条件式を含めることが可能です。以下はドキュメントに記載がある例の引用ですが、この SQL ファイルを作成しリモートリポジトリに Push しリポジトリステージを更新し、実行します。

--!jinja2

{% if DEPLOYMENT_TYPE == 'prod' %}
  {% set environments = ['prod1', 'prod2'] %}
{% else %}
  {% set environments = ['dev', 'qa', 'staging'] %}
{% endif %}

{% for environment in environments %}
  CREATE DATABASE {{ environment }}_db;
  USE DATABASE {{ environment }}_db;
  CREATE TABLE {{ environment }}_orders (
    id NUMBER,
    item VARCHAR,
    quantity NUMBER);
  CREATE TABLE {{ environment }}_customers (
    id NUMBER,
    name VARCHAR);
{% endfor %}

実行後クエリ履歴を確認すると、指定のパラメータをもとに条件分岐し environments（'dev', 'qa', 'staging’）ごとにデータベース・テーブルが作成されています。

snowflake-git-stage-jinja2-15

さいごに

Snowflake から Git リポジトリにアクセスできるリポジトリステージと Jinja2 テンプレートを使用したステージ上のファイルを実行できるEXECUTE IMMEDIATE FROMを試してみました。
Snowflake 環境の初期セットアップや、異なるアカウントで同じ環境を用意したい場合に使用できる機能かと思います。
こちらの内容が何かの参考になれば幸いです。